|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
System Global Area (SGA)
The SGA is a shared memory region Oracle uses to store data and control information for one Oracle instance. The SGA is allocated when the Oracle instance starts; it is deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA is made up of the database buffers, the redo log buffer, and the shared pool; each has a fixed size and is created at instance startup.
- Database buffer cache. The buffer cache stores the most recently used data blocks. These blocks can contain modified data that has not yet been written to disk (sometimes known as dirty blocks), blocks that have not been modified, or blocks that have been written to disk since modification (sometimes known as clean blocks). Because the buffer cache keeps blocks based on a most recently used algorithm, the most active buffers stay in memory to reduce I/O and improve performance.
- Redo log buffer. The redo log buffer of the SGA stores redo entries or a log of changes made to the database. The redo log buffers are written to the redo log as quickly and efficiently as possible. Remember that the redo log is used for instance recovery in the event of a system failure.
- Shared pool. The shared pool is the area of the SGA that stores shared memory structures such as shared SQL areas. Shared SQL areas store the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, the shared SQL area can be accessed by each of them to reduce the amount of memory needed and to reduce the processing time used for parsing and execution planning.
Program Global Area (PGA)
The PGA is a memory area that contains data and control information for the Oracle server processes. The amount and content of the PGA depends on the Oracle server options you have installed. The PGA is made up of the following components:
- Stack space. This is the memory that holds the session variables, arrays, and so on.
- Session information. If you are not running the multithreaded server, the session information is stored in the PGA. If you are running the multithreaded server, the session information is stored in the SGA.
- Private SQL area. This is an area in the PGA where information such as binding variables and runtime buffers are kept.
Processes
The term process is used in this book to describe a thread of execution, or a mechanism that can execute a set of code. In many operating systems, traditional processes have been replaced with threads or lightweight processes. In this book, the term process refers to the mechanism of execution and can refer to either a traditional process or a thread.
The Oracle RDBMS uses two types of processes: the user processes and the Oracle processes (also known as background processes).
User Processes
User, or client, processes are the users connections into the RDBMS system. The user process manipulates the users input and communicates with the Oracle server process through the Oracle program interface. The user process is also used to display the information requested by the user and, if necessary, can process this information into a more useful form.
Oracle Processes
The Oracle processes perform functions for the users. The Oracle processes can be split into two groups: server processes (which perform functions for the invoking process) and background processes (which perform functions on behalf of the entire RDBMS).
Server Processes (Shadow Processes)
The server processes, also known as shadow processes, communicate with the user and interact with Oracle to carry out the users requests. For example, if the user process requests a piece of data not already in the SGA, the shadow process is responsible for reading the data blocks from the data files into the SGA. There can be a one-to-one correlation between the user processes and the shadow processes (as in a dedicated server configuration); although one shadow process can connect to multiple user processes (as in a multithreaded server configuration), doing so reduces the utilization of system resources.
Background Processes
Background processes are the Oracle processes used to perform various tasks within the RDBMS system. These tasks vary from communicating with other Oracle instances, performing system maintenance and cleanup, to writing dirty blocks to disk. Here are brief descriptions of the nine Oracle background processes:
- DBWR (Database Writer). DBWR is responsible for writing dirty data blocks from the database block buffers to disk. When a transaction changes data in a data block, it is not necessary for that data block to be immediately written to disk. Because of this, the DBWR can write this data out to disk in a manner that is more efficient than writing when each transaction completes. Usually, the DBWR writes only when the database block buffers are needed for data to be read in. When data is written out, it is done in a least recently used fashion. For systems in which Asynchronous I/O (AIO) is available, there should be only one DBWR process. For systems in which AIO is not available, performance can be greatly enhanced by adding more DBWR processes.
- LGWR (Log Writer). The LGWR process is responsible for writing data from the log buffer to the redo log.
- CKPT (Checkpoint process). The CKPT process is responsible for signaling the DBWR process to perform a checkpoint and to update all the data and control files for the database to indicate the most recent checkpoint. A checkpoint is an event in which all modified database buffers are written to the data files by the DBWR. The CKPT process is optional. If the CKPT process is not present, the LGWR process assumes these responsibilities.
- PMON (Process Monitor). PMON is responsible for keeping track of database processes and cleaning up if a process prematurely dies (PMON cleans up the cache and frees resources that may still be allocated). PMON is also responsible for restarting any dispatcher processes that may have failed.
- SMON (System Monitor). SMON performs instance recovery at instance startup. This includes cleaning up temporary segments and recovering transactions that have died because of a system crash. SMON also defragments the database by coalescing free extents within the database.
- RECO (Recovery process). RECO is used to clean up transactions that were pending in a distributed database. RECO is responsible for committing or rolling back the local portion of the disputed transactions.
- ARCH (Archiver process). ARCH is responsible for copying the online redo log files to archival storage when they become full. ARCH is active only when the RDBMS is operated in ARCHIVELOG mode. When a system is not operated in ARCHIVELOG mode, it may not be possible to recover after a system failure. You should always operate in ARCHIVELOG mode.
- LCKn (Parallel Server Lock processes). Up to 10 LCK processes are used for interinstance locking when the Oracle Parallel Server option is used.
- Dnnn (Dispatcher processes). When the Multithreaded Server option is used, at least one dispatcher process is used for every communications protocol in use. The dispatcher process is responsible for routing requests from the user processes to available shared server processes and back.
|